const { app } = require('electron')
const { join } = require('path')
const sqlite3 = require('sqlite3')
 
const userDataPath = app.getPath("userData");
const dbPath = join(userDataPath, "database.db");
 
class Database {
  static instance;
  db;
 
  constructor() {
    this.db = new sqlite3.Database(dbPath);
  }
 
  static async getInstance() {
    if (!Database.instance) {
      Database.instance = new Database();
      await Database.instance.open();
      await Database.instance.initializeSchema();
    }
    return Database.instance;
  }
 
  open() {
    return new Promise((resolve, reject) => {
      this.db.serialize(() => {
        this.db.run("PRAGMA foreign_keys = ON", (err) => {
          if (err) {
            reject(err);
          } else {
            console.log("Connected to the database.");
            resolve();
          }
        });
      });
    });
  }
 
  initializeSchema() {
    return this.query({
      sql: `
      CREATE TABLE IF NOT EXISTS t_user (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        idCardNo TEXT,
        gender TEXT,
        age INTEGER,
        birthDay TEXT,
        nation INTEGER,
        doctorType TEXT,
        doctorLevel TEXT,
        certNo TEXT,
        doctorScope TEXT,
        licenseNo TEXT,
        postQualification TEXT,
        state TEXT DEFAULT 已激活,
        isModified TEXT,
        auditTime TEXT,
        eCertState TEXT DEFAULT 未申领,
        isYtbRegisted TEXT DEFAULT 未医通办注册,
        orgName TEXT,
        orgAddress TEXT,
        startDate TEXT,
        endDate TEXT,
        school TEXT,
        profession TEXT,
        education TEXT,
        approvalDate TEXT,
        approvalAgency TEXT,
        avatar BLOB,
        avatarBuffer BLOB
      )
    `,
    }).then(() => {
      console.log("Database schema initialized.");
    }).catch((err) => {
      console.error("Error initializing database schema:", err);
    });
  }
 
  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) {
          reject(err);
        } else {
          console.log("Database closed.");
          resolve();
        }
      });
    });
  }
 
  query(param) {
    return new Promise((resolve, reject) => {
      this.db.all(param.sql, param.params, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }
 
  insert(param) {
    return new Promise((resolve, reject) => {
      const keys = Object.keys(param.data);
      const values = Object.values(param.data);
      const placeholders = keys.map(() => "?").join(",");
      const sql = `INSERT INTO ${param.table} (${keys.join(
        ","
      )}) VALUES (${placeholders})`;
 
      this.db.run(sql, values, function (err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.lastID);
        }
      });
    });
  }
 
  update(param) {
    return new Promise((resolve, reject) => {
      const entries = Object.entries(param.data)
        .map(([key, value]) => `${key} = ?`)
        .join(",");
      const params = Object.values(param.data);
      const sql = `UPDATE ${param.table} SET ${entries} WHERE ${param.condition}`;
 
      this.db.run(sql, params, function (err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.changes);
        }
      });
    });
  }
 
  delete(param) {
    return new Promise((resolve, reject) => {
      const sql = `DELETE FROM ${param.table} WHERE ${param.condition}`;
 
      this.db.run(sql, (err) => {
        if (err) {
          reject(err);
        } else {
          resolve();
        }
      });
    });
  }
}
 
// Wrap database calls to ensure initialization
const getDatabase = async () => {
  return await Database.getInstance();
};
 
exports.executeQuery = async (param) => {
  const db = await getDatabase();
  return db.query(param);
};
 
exports.executeInsert = async (param) => {
  const db = await getDatabase();
  return db.insert(param);
};
 
exports.executeUpdate = async (param) => {
  const db = await getDatabase();
  return db.update(param);
};
 
exports.executeDelete = async (param) => {
  const db = await getDatabase();
  return db.delete(param);
};
